package com.maykk.stu_manager.model;

import com.maykk.stu_manager.global.JdbcUtils;
import com.maykk.stu_manager.global.Student;
import com.maykk.stu_manager.views.StuPage;

import java.sql.*;
import java.util.ArrayList;

/**
 * @ClassName: StuModel
 * @Description: TODO 学生全局类
 * @Author: MaykkStar@163.com
 */
public class StuModel {
    private static Connection conn = null;
    private static PreparedStatement pst = null;
    private static ResultSet rs = null;

    /**
     * 查找所有
     *
     * @return 所有结果
     * @throws SQLException SQL 异常
     */
    public ArrayList<Student> selectAll() throws SQLException {
        String sql = "select id, name, sex, age, score, tel, classid from stu";
        conn = JdbcUtils.getConnection();
        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery();
        ArrayList<Student> stuList = new ArrayList<>();
        while (rs.next()) {
            stuList.add(new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("sex") == 1 ? "男" : "女", rs.getInt("age"), rs.getFloat("score"), rs.getString("tel"), rs.getString("classid")));
        }
        JdbcUtils.free(rs, pst, conn);
        return stuList;
    }

    /**
     * 根据学号查询
     *
     * @param id 学号
     * @return 查找结果
     * @throws SQLException
     */
    public ArrayList<Student> findById(int id) throws SQLException {
        String sql = "select id, name, sex, age, score, tel, classid from stu where id = ?";
        conn = JdbcUtils.getConnection();
        pst = conn.prepareStatement(sql);
        pst.setInt(1, id);
        rs = pst.executeQuery();
        ArrayList<Student> stuList = new ArrayList<>();
        while (rs.next()) {
            stuList.add(new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("sex") == 1 ? "男" : "女", rs.getInt("age"), rs.getFloat("score"), rs.getString("tel"), rs.getString("classid")));
        }
        JdbcUtils.free(rs, pst, conn);
        return stuList;
    }

    /**
     * 根据姓名查找
     *
     * @param name 学生姓名
     * @return 查找结果
     * @throws SQLException SQL 异常
     */
    public ArrayList<Student> findByName(String name) throws SQLException {
        String sql = "select id, name, sex, age, score, tel, classid from stu where name = ?";
        conn = JdbcUtils.getConnection();
        pst = conn.prepareStatement(sql);
        pst.setString(1, name);
        rs = pst.executeQuery();
        ArrayList<Student> stuList = new ArrayList<>();
        while (rs.next()) {
            stuList.add(new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("sex") == 1 ? "男" : "女", rs.getInt("age"), rs.getFloat("score"), rs.getString("tel"), rs.getString("classid")));
        }
        JdbcUtils.free(rs, pst, conn);
        return stuList;
    }

    /**
     * 根据性别查找
     *
     * @param sex 性别
     * @return 查询结果
     */
    public ArrayList<Student> findBySex(int sex) throws SQLException {
        System.out.println(sex);
        String sql = "select id, name, sex, age, score, tel, classid from stu where sex = ?";
        conn = JdbcUtils.getConnection();
        pst = conn.prepareStatement(sql);
        pst.setInt(1, (sex + 1));
        rs = pst.executeQuery();
        ArrayList<Student> stuList = new ArrayList<>();
        while (rs.next()) {
            stuList.add(new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("sex") == 1 ? "男" : "女", rs.getInt("age"), rs.getFloat("score"), rs.getString("tel"), rs.getString("classid")));
        }
        JdbcUtils.free(rs, pst, conn);
        return stuList;
    }

    /**
     * 根据年龄查找
     *
     * @param age 学生年龄
     * @return 返回查找到的学生
     * @throws SQLException SQL 异常
     */
    public ArrayList<Student> findByAge(int age) throws SQLException {
        String sql = "select id, name, sex, age, score, tel, classid from stu where age = ?";
        conn = JdbcUtils.getConnection();
        pst = conn.prepareStatement(sql);
        pst.setInt(1, age);
        rs = pst.executeQuery();
        ArrayList<Student> stuList = new ArrayList<>();
        while (rs.next()) {
            stuList.add(new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("sex") == 1 ? "男" : "女", rs.getInt("age"), rs.getFloat("score"), rs.getString("tel"), rs.getString("classid")));
        }
        JdbcUtils.free(rs, pst, conn);
        return stuList;
    }

    /**
     * 根据成绩查找
     *
     * @param score 用户输入的成绩
     * @return 查找结果
     * @throws SQLException SQL 异常
     */
    public ArrayList<Student> findByScore(float score) throws SQLException {
        String sql = "select id, name, sex, age, score, tel, classid from stu where score = ?";
        conn = JdbcUtils.getConnection();
        pst = conn.prepareStatement(sql);
        pst.setFloat(1, score);
        rs = pst.executeQuery();
        ArrayList<Student> stuList = new ArrayList<>();
        while (rs.next()) {
            stuList.add(new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("sex") == 1 ? "男" : "女", rs.getInt("age"), rs.getFloat("score"), rs.getString("tel"), rs.getString("classid")));
        }
        JdbcUtils.free(rs, pst, conn);
        return stuList;
    }

    /**
     * 根据班级查找
     *
     * @param classid 班级
     * @return 查找结果
     * @throws SQLException SQL 异常
     */
    public ArrayList<Student> findByClass(String classid) throws SQLException {
        String sql = "select id, name, sex, age, score, tel, classid from stu where classid = ?";
        conn = JdbcUtils.getConnection();
        pst = conn.prepareStatement(sql);
        pst.setString(1, classid);
        rs = pst.executeQuery();
        ArrayList<Student> stuList = new ArrayList<>();
        while (rs.next()) {
            stuList.add(new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("sex") == 1 ? "男" : "女", rs.getInt("age"), rs.getFloat("score"), rs.getString("tel"), rs.getString("classid")));
        }
        JdbcUtils.free(rs, pst, conn);
        return stuList;
    }

    /**
     * 添加
     *
     * @param student 要添加的对象
     */
    public void addStu(Student student) throws SQLException {
        if (judgeTel(student.getTel())) {
            System.out.println("联系方式已存在，请重新添加!~");
        } else {
            String sql = "insert into stu(id,name,sex,age,score,tel,classid) values(?,?,?,?,?,?,?)";
            String sex = student.getSex().equals("女") ? "0" : "1";
            conn = JdbcUtils.getConnection();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, student.getId());
            pst.setString(2, student.getName());
            pst.setString(3, sex);
            pst.setInt(4, student.getAge());
            pst.setFloat(5, student.getScore());
            pst.setString(6, student.getTel());
            pst.setString(7, student.getClassid());
            int lows = pst.executeUpdate();
            if (lows > 0) {
                StuPage.Success("添加成功");
            }
        }
        JdbcUtils.free(rs, pst, conn);
    }

    /**
     * 判断数据库内联系方式是否存在
     *
     * @param tel 联系方式
     * @return true 存在， false 不存在
     * @throws SQLException SQL 异常
     */
    public static boolean judgeTel(String tel) throws SQLException {
        String sql = "select tel from stu where tel = ?";
        conn = JdbcUtils.getConnection();
        pst = conn.prepareStatement(sql);
        pst.setString(1, tel);
        rs = pst.executeQuery();
        return rs.next();
    }

    /**
     * 修改学生信息
     *
     * @param updateStuId   原信息
     * @param updateStuInfo 新信息
     * @return 几行数据受影响，1 成功
     * @throws SQLException
     */
    public int updateStu(ArrayList<Student> updateStuId, Student updateStuInfo) throws SQLException {
        if (updateStuId.isEmpty()) {
            System.out.println("未找到该学号的学生，请重新输入学号！~");
        } else {
            String sql = "update stu set id = ?, name = ?, sex = ?, age = ?, score = ?, tel = ?, classid = ? where id = ?;";
            Connection conn = JdbcUtils.getConnection();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, updateStuInfo.getId());
            pst.setString(2, updateStuInfo.getName());
            pst.setString(3, updateStuInfo.getSex());
            pst.setInt(4, updateStuInfo.getAge());
            pst.setDouble(5, updateStuInfo.getScore());
            pst.setString(6, updateStuInfo.getTel());
            pst.setString(7, updateStuInfo.getClassid());
            pst.setInt(8, updateStuId.get(0).getId());
        }
        return pst.executeUpdate();
    }

    /**
     * 删除学生
     *
     * @param deleteStuId 要删除的学生学号
     * @return
     * @throws SQLException
     */
    public int deleteStu(int deleteStuId) throws SQLException {
        String sql = "delete from stu where id = ?";
        Connection conn = JdbcUtils.getConnection();
        pst = conn.prepareStatement(sql);
        pst.setInt(1, deleteStuId);
        return pst.executeUpdate();
    }
}
